with 
--循环袋异常信息表
dwd_auto_rb_damaged_arbitrate_base_dt as (
    select 
          t.rfid         --芯片号
         ,t.damage_type  --异常类型
         , row_number()over(partition by t.rfid order by t.create_time desc )  rn   
    from jms_dwd.dwd_auto_rb_damaged_arbitrate_base_dt t 
    where t.dt between date_add('{{ execution_date  | cst_ds }}', -30) and '{{ execution_date  | cst_ds }}'
)
--循环带扫描记录表  
,dwd_auto_rb_scan_record_base_dt as (

select *
       ,lead(named_struct_flg)over(partition by t.rfid order by rn ) as dw_named_struct_flg
       ,lag (named_struct_flg)over(partition by t.rfid order by rn ) as up_named_struct_flg
 from (
            select
                 t.scan_id                                            --扫描id
                ,t.rfid
                ,named_struct(
                             'scan_time', scan_time                   --扫描时间
                             ,'scan_type_code',scan_type_code         --扫描类型编号
                             ,'scan_type_name',scan_type_name         --扫描类型名称
                             ,'scan_network_code',scan_network_code   --扫描网点编号
                             ,'scan_network_name',scan_network_name   --扫描网点名称
                             ) as named_struct_flg
                ,row_number () over(partition by t.rfid order by t.scan_time) as rn
            from jms_dwd.dwd_auto_rb_scan_record_base_dt t
            where t.dt between date_add('{{ execution_date  | cst_ds }}', -30) and '{{ execution_date  | cst_ds }}'
            
        ) t
)

--循环袋库存变更表
insert overwrite table jms_dm.dm_repair_access_detail_dt partition (dt)
select 
      t.scan_network_code       --维修点编码
     ,t.scan_network_name       --维修点名称
     ,t.rfid                    --rfid
     ,t.material_type           --物料类别
     ,t.material_details as warehouse_type          --物料类型
     ,t.scan_time               --创建时间
     ,case when t.scan_type_code='07' and   t1.damage_type is not null then 1 else 0 end  as  is_damage    --是否仲裁
     ,case when t.scan_type_code='07' then  t1.damage_type   else null end as damage_type      --仲裁类型  
     ,case when t.scan_type_code<>'07' and t2.dw_named_struct_flg is not null then 1 else 0 end  as dw_operate --是否有后续操作
     
     ,case when t.scan_type_code='07' then t2.up_named_struct_flg.scan_type_code    else null end  as   up_scan_type_code     --上一站扫描类型
     ,case when t.scan_type_code='07' then t2.up_named_struct_flg.scan_type_name    else null end  as   up_scan_type_name     --上一站扫描类型code
     ,case when t.scan_type_code='07' then t2.up_named_struct_flg.scan_time         else null end  as   up_scan_time          --上一站扫描时间
     ,case when t.scan_type_code='07' then t2.up_named_struct_flg.scan_network_code else null end  as   up_scan_network_code  --上一站扫描网点code
     ,case when t.scan_type_code='07' then t2.up_named_struct_flg.scan_network_name else null end  as   up_scan_network_name  --上一站扫描网点
     
     ,case when t.scan_type_code<>'07' then t2.dw_named_struct_flg.scan_type_code    else null end  as  dw_scan_type_code      --下一站扫描类型
     ,case when t.scan_type_code<>'07' then t2.dw_named_struct_flg.scan_type_name    else null end  as  dw_scan_type_name      --下一站扫描类型code
     ,case when t.scan_type_code<>'07' then t2.dw_named_struct_flg.scan_time         else null end  as  dw_scan_time           --下一站扫描时间
     ,case when t.scan_type_code<>'07' then t2.dw_named_struct_flg.scan_network_code else null end  as  dw_scan_network_code   --下一站扫描网点code
     ,case when t.scan_type_code<>'07' then t2.dw_named_struct_flg.scan_network_name else null end  as  dw_scan_network_name   --下一站扫描网点
     
     ,scan_type_code   --出入库类型,07-维修入库,11-注销,34-维修出库  
     ,to_date(t.scan_time) as date_time
     ,to_date(t.scan_time) as dt 
from jms_dwd.dwd_auto_rb_stock_change_record_base_dt t 
left join  dwd_auto_rb_damaged_arbitrate_base_dt t1 on  t1.rfid=t.rfid and t1.rn=1
left join  dwd_auto_rb_scan_record_base_dt t2       on  t2.scan_id=t.scan_id  and t2.rfid=t.rfid
where t.dt between date_add('{{ execution_date  | cst_ds }}', -30) and '{{ execution_date  | cst_ds }}'
  and to_date(t.scan_time) between date_add('{{ execution_date  | cst_ds }}', -15) and '{{ execution_date  | cst_ds }}'
  and t.scan_type_code  in ('11','34','07')
  and t.change_type=1
 distribute by pmod(hash(rand()), 120)
;
